#连接fdc_ext 创建ods层表
import psycopg2
from configparser import ConfigParser
import os
from datetime import datetime
from datetime import timedelta

#建表语句模板
colString = ''' %s %s,\n'''

drop_table_parttern='''drop table if exists %s.%s;'''
partition_parttern='''PARTITION %s VALUES LESS THAN ('%s'),\n''';
dws_ddl_partten='''
create table %s.%s ( 
%s
partitiondate date
)
WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE)
DISTRIBUTE BY HASH (%s)
PARTITION BY RANGE (partitiondate)(
	%s
);
'''

#insert 插入模板
insert_col_parttern=''',%s \n '''
insert_parttern='''
/*
作者: 田川
日期: %s
*/
alter table  fdc_ods.%s truncate partition for ('${partitiondate}');
insert  into fdc_ods.%s (
%s,partitiondate
) select 
 %s,'${partitiondate}'--分区日期
 from fdc_ext.%s
;
'''
# 常数
section_name = 'mydws'
d=dict()

class Config(object):
   #初始化cfg
   def __init__(self,config_path='mydb.cnf'):
      file_path=os.path.join(os.path.dirname(__file__),config_path)
      self.cfg=ConfigParser()
      self.cfg.read(filenames=file_path,encoding='utf-8')
      print(self.cfg.sections())
   #获取指定key的values
   def getsection(self,section,key):
      return self.cfg.get(section=section,option=key)
#查询数据，获取指定表的列名
conf=Config()
readfile=conf.getsection(section='mypath',key='readfile')
outpath=conf.getsection(section='mypath',key='outpath')
outddl=conf.getsection(section='mypath',key='outddl')

def do(table_name,table_schema):
   conn = psycopg2.connect(database=conf.getsection(section_name,'db_name')
                         , user=conf.getsection(section_name,"user")
                        , password=conf.getsection(section_name,'password')
                        , host=conf.getsection(section_name,'host')
                        , port=conf.getsection(section_name,'port'))
   cur = conn.cursor()
   cur.execute("select column_name,ordinal_position from information_schema.columns where table_name='"+table_name+"'  and  table_schema='"+table_schema+"'")
   rows = cur.fetchall()
   d.clear()
   for row in rows:
       d[row[1]]=row[0]
   conn.close()
#定义输出文件
out=open(file=outddl,mode='w',encoding='utf-8')
#定义生成ddl

## 定义生成分区开始日期和结束日期，默认开始日期是当前日期
def gen_ddl(d,table_name,end_date,start_date=datetime.now()):
   out.write(drop_table_parttern % ('fdc_ods',table_name.replace('ext','ods')))
   fisrt_col=str()
   col_file=str()
   partition_range=str()
   d2=datetime(year=int(end_date[:4]), month=int(end_date[5:7]),day=int(end_date[8:10]))
   val=(d2-start_date).days
   for i in range(0,val+2):
      pvalue=(start_date+timedelta(i)).strftime('%Y-%m-%d')
      pname='p_{}'.format((start_date+timedelta(i-1)).strftime('%Y-%m-%d').replace('-',''))
      partition_range+=partition_parttern%(pname,pvalue)
   # print(partition_range[0:-2])
   for key,value in sorted(d.items(),key=lambda s:s[0]):
       if key==1 :
         fisrt_col=value
       col_file+=colString % (value.lower(),'varchar')
   index=col_file.rfind(',')
   col_file=col_file[:index+1]
   out.write(dws_ddl_partten % ('fdc_ods',table_name.replace('ext','ods'),col_file,fisrt_col,partition_range[0:-2]))   
   

# 定义生成insert 语句
def gen_dws_insert(d,table_name):
   col_file=str()
   for key,value in sorted(d.items(),key=lambda s:s[0]):
       col_file+=insert_col_parttern % value.lower()
   index=col_file.find(',')
   col_file=col_file[index+1:]
   with open(file=outpath+table_name.replace('ext','ods')+'.sql',mode='w',encoding='utf-8') as outf:
        outf.write(insert_parttern %(str(datetime.now())
                    ,table_name.replace('ext','ods'),table_name.replace('ext','ods'),col_file,col_file,table_name))
   pass



with open(file=readfile,mode='r' ,encoding='utf-8') as input:
	for tablename in input.readlines():
		do(tablename.strip(),'fdc_ext')
		gen_ddl(d,tablename.strip(),'2023-01-01')
		gen_dws_insert(d,tablename.strip())
out.close()
